package studentDao.studentImpl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import po.Room;
import po.Student;
import studentDao.StudentDao;
import studentDao.Dbconnection.StuDbconnection;

public class StudentImpl implements StudentDao{
	String sql = null;
	int result = 0;
//	List<Student> studentList= new ArrayList<Student>();
	
	@Override
	public List<Student> findAll() {
		Connection con = null;
		PreparedStatement ps = null; 
		ResultSet rs = null;
		List<Student> studentList= new ArrayList<Student>();
		try {
			con = StuDbconnection.getConnection();
			sql = "SELECT student_id,student_name,student_birthday,student_telephoneNumber,student_address,student_email,student.room_no,room_name FROM student,room WHERE student.room_no=room.room_no";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) { 
				String studentId = rs.getString(1);
				String studentName = rs.getString(2);
				Date studentBirthday = rs.getDate(3);
				String studentTelephoneNumber = rs.getString(4);
				String studentAddress = rs.getString(5);
			    String studentEmail =rs.getString(6);
			    int roomNo = rs.getInt(7);
			    String roomName = rs.getString(8);
		 	    Room room = new Room(roomNo,roomName);
			    Student stu = new Student(studentId,studentName,studentBirthday,studentTelephoneNumber,studentAddress,studentEmail, room);
			    studentList.add(stu);
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}finally {
			StuDbconnection.close(rs, ps);
		}
		return studentList;
	}

	@Override
	public void save(Student x) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = StuDbconnection.getConnection();
			sql = "INSERT INTO student VALUE(?,?,?,?,?,?,?)";
			ps = con.prepareStatement(sql);
			ps.setString(1,x.getStudentId());
			ps.setString(2, x.getStudentName());
			ps.setDate(3, x.getStudentBirthday());
			ps.setString(4, x.getStudentTelephoneNumber());
			ps.setString(5,x.getStudentAddress());
			ps.setString(6,x.getStudentEmail());
			ps.setInt(7, x.getRoom().getRoomNo());
//			ps.setInt(8, x.getRoom().getRoomNo());
//			ps.setString(9,  x.getRoom().getRoomName());
			result = ps.executeUpdate();
			System.out.println("操作结果是"+result);
		}catch(SQLException e) {
			e.printStackTrace();
		}finally{
			StuDbconnection.close(rs, ps);
		}
	}

	@Override
	public void delete(String x) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = StuDbconnection.getConnection();
			sql = "DELETE FROM student WHERE student_id=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, x);
			result = ps.executeUpdate();
			System.out.println("操作结果是" + result);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			StuDbconnection.close(rs, ps);
		}
	}

	@Override
	public void update(Student x) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = StuDbconnection.getConnection();
			sql = "UPDATE student SET student_name=?,student_birthday=?,student_telephoneNumber=?,student_address=?,student_email=?,student.room_no=? WHERE student_id=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, x.getStudentName());
			ps.setDate(2, x.getStudentBirthday());
			ps.setString(3, x.getStudentTelephoneNumber());
			ps.setString(4,x.getStudentAddress());
			ps.setString(5,x.getStudentEmail());
			ps.setInt(6, x.getRoom().getRoomNo());
			ps.setString(7, x.getStudentId());
			result = ps.executeUpdate();
			System.out.println("操作结果是"+result);
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			StuDbconnection.close(rs, ps);
		}	
	}
	/**
	 *学号查询
	 */
	public List<Student> get(String x){
		Connection con = null;
		PreparedStatement ps = null; 
		ResultSet rs = null;	
		List<Student> studentList= new ArrayList<Student>();
		try {
			con = StuDbconnection.getConnection();
			sql = "SELECT student_id,student_name,student_birthday,student_telephoneNumber,student_address,student_email,student.room_no,room_name FROM student,room WHERE student.room_no=room.room_no AND student_id=?";
			ps = con.prepareStatement(sql);//因为student.room_no=room.room_no 所以select只写一个表的roomNo即可
			ps.setString(1, x);
			rs = ps.executeQuery();
			while(rs.next()) { 
				String studentId = rs.getString(1);
				String studentName = rs.getString(2);
				Date studentBirthday = rs.getDate(3);
				String studentTelephoneNumber = rs.getString(4);
				String studentAddress = rs.getString(5);
			    String studentEmail =rs.getString(6);
			    int roomNo = rs.getInt(7);
			    String roomName = rs.getString(8);
		 	    Room room = new Room(roomNo,roomName);
			    Student stu = new Student(studentId,studentName,studentBirthday,studentTelephoneNumber,studentAddress,studentEmail, room);
			    studentList.add(stu);
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}finally {
			StuDbconnection.close(rs, ps);
		}
		return studentList;
	}
	/**
	 *学号查询 但返回的是学号 用于判断 修改 和 删除 输入学号时 是否存在该学号学生
	 */
	public String getId(String x){
		Connection con = null;
		PreparedStatement ps = null; 
		ResultSet rs = null;	
		String studentId = null;
		try {
			con = StuDbconnection.getConnection();
			sql = "SELECT student_id FROM student WHERE student_id=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, x);
			rs = ps.executeQuery();
			while(rs.next()) { 
				studentId = rs.getString(1);
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}finally {
			StuDbconnection.close(rs, ps);
		}
		return studentId;
	}
	//通过宿舍号查询
	public List<Student> getByRoomName(String x){
		Connection con = null;
		PreparedStatement ps = null; 
		ResultSet rs = null;	
		List<Student> studentList= new ArrayList<Student>();
		try {
			con = StuDbconnection.getConnection();
			sql = "SELECT student_id,student_name,student_birthday,student_telephoneNumber,student_address,student_email,student.room_no,room_name FROM student,room WHERE student.room_no=room.room_no AND room.room_name=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, x);
			rs = ps.executeQuery();
			while(rs.next()) { 
				String studentId = rs.getString(1);
				String studentName = rs.getString(2);
				Date studentBirthday = rs.getDate(3);
				String studentTelephoneNumber = rs.getString(4);
				String studentAddress = rs.getString(5);
			    String studentEmail =rs.getString(6);
			    int roomNo = rs.getInt(7);
			    String roomName = rs.getString(8);
		 	    Room room = new Room(roomNo,roomName);
			    Student stu = new Student(studentId,studentName,studentBirthday,studentTelephoneNumber,studentAddress,studentEmail, room);
			    studentList.add(stu);
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}finally {
			StuDbconnection.close(rs, ps);
		}
		return studentList;
	}

	@Override
	public Student getReturnStudent(String x) {
		Connection con = null;
		PreparedStatement ps = null; 
		ResultSet rs = null;	
		List<Student> studentList= new ArrayList<Student>();
		Student stu = null;
		try {
			con = StuDbconnection.getConnection();
			sql = "SELECT student_id,student_name,student_birthday,student_telephoneNumber,student_address,student_email,student.room_no,room_name FROM student,room WHERE student.room_no=room.room_no AND student_id=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, x);
			rs = ps.executeQuery();
			while(rs.next()) { 
				String studentId = rs.getString(1);
				String studentName = rs.getString(2);
				Date studentBirthday = rs.getDate(3);
				String studentTelephoneNumber = rs.getString(4);
				String studentAddress = rs.getString(5);
			    String studentEmail =rs.getString(6);
			    int roomNo = rs.getInt(7);
			    String roomName = rs.getString(8);
		 	    Room room = new Room(roomNo,roomName);
			    stu = new Student(studentId,studentName,studentBirthday,studentTelephoneNumber,studentAddress,studentEmail, room);
			    studentList.add(stu);
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}finally {
			StuDbconnection.close(rs, ps);
		}
		return stu;
	}
}
